from . import *

import redis

from yyxt.settings import BASE_DIR
from django.http import FileResponse

import openpyxl
from openpyxl.styles.fills import PatternFill
from openpyxl.utils.cell import get_column_letter
from openpyxl.styles import Alignment

"""
=============================================================
1:附件上传操作，文件保存到本地MEDIA_ROOT;
2:附件删除操作;
3:文件读取生成器;
4:视频流生成器, 每节8M;
5:验收记录将视频文件以流媒体的方式响应;
6:下载附件，pk是一个，可以多个迭代下载;
7:excel表格模版下载，用于B端导入表格格式下载;
8:(下载Excel表格);
9:导入excel数据到数据库;
=============================================================
"""


def attachment_uploading(request, model):
    """1:附件上传操作，文件保存到本地MEDIA_ROOT;
    前端传递："pk"-->queryset "id"；"accessory"：上传的文件列表
    :param model: Accessory外键链接模型对象
    :return:-->dict:文件上传结果信息
    """
    # 获取文件存储连接对象id值，【申请单对应附件】
    pk = request.POST.get('pk')
    # 获取被上传文件对象
    incoming = model.objects.get(pk=pk)
    # 获取前端传来的文件对象
    accessorys = request.FILES.getlist('accessory')
    data = {}  # 建立文件上传状态信息dict

    for f in accessorys:
        # 判断表中"accessory"是否存在上传的文件,没有保存到数据库中，django自动保存到MEDIA_ROOT
        obj, create = Accessory.objects.get_or_create(accessory=f)
        # (< Accessory: Accessory object >, False)
        if create:  # 上传操作了
            obj.table_id = incoming  # 文件外键连接到当前incoming对象
            obj.save()
            data.update({str(f): "上传成功!"})
        else:
            data.update({str(f): "该文件已存在，请删除后再上传!"})

    return data


def attachment_delete(request):
    """
    2:附件删除操作
    :param request:
    :return: data
    """
    pk = request.POST.get('pk')
    files_obj = Accessory.objects.filter(table_id=pk)
    files_name = files_obj.values('accessory')
    data = {}
    for f in files_name:  # 删除dir文件
        DIR = os.path.join(BASE_DIR, 'media', str(f['accessory']))
        # print(DIR)
        if os.path.exists(DIR):
            os.remove(DIR)
            data.update({str(f['accessory']): '该文件已删除'})
        else:
            data.update({str(f['accessory']): '该文件不存在'})
    for f in files_obj:  # 删除数据库dir
        f.delete()
    return data


def file_iterator(file_name, chunk_size=512):
    """3:文件读取生成器"""
    with open(file_name, 'rb') as f:
        while True:
            date = f.read(chunk_size)
            if date:
                yield date
            else:
                break


def video_iterator(file_name, chunk_size=8192, offset=0, length=None):
    """
    4:视频流生成器, 每节8M
    :param file_name: 文件路径名
    :param chunk_size: 每节流大小
    :param offset: 偏移量
    :param length: 视频长度
    :return:
    """
    with open(file_name, "rb") as f:
        f.seek(offset, os.SEEK_SET)
        remaining = length
        while True:
            bytes_length = chunk_size if remaining is None else min(remaining,
                                                                    chunk_size)
            data = f.read(bytes_length)
            if not data:
                break
            if remaining:
                remaining -= len(data)
            yield data


def video_response(request, path: str) -> StreamingHttpResponse:
    """5:验收记录将视频文件以流媒体的方式响应"""
    range_header = request.META.get('HTTP_RANGE', '').strip()
    range_re = re.compile(r'bytes\s*=\s*(\d+)\s*-\s*(\d*)', re.I)
    range_match = range_re.match(range_header)
    size = os.path.getsize(path)
    content_type, encoding = mimetypes.guess_type(path)
    content_type = content_type or 'application/octet-stream'
    if range_match:
        first_byte, last_byte = range_match.groups()
        first_byte = int(first_byte) if first_byte else 0
        last_byte = first_byte + 1024 * 1024 * 10
        if last_byte >= size:
            last_byte = size - 1
        length = last_byte - first_byte + 1
        resp = StreamingHttpResponse(
            video_iterator(path, offset=first_byte, length=length),
            status=206,
            content_type=content_type)
        resp['Content-Length'] = str(length)
        resp['Content-Range'] = 'bytes %s-%s/%s' % (
        first_byte, last_byte, size)
    else:
        resp = StreamingHttpResponse(FileWrapper(open(path, 'rb')),
                                     content_type=content_type)
        resp['Content-Length'] = str(size)
    resp['Accept-Ranges'] = 'bytes'
    return resp


def attachment_download(request):
    """
    6:下载附件，pk是一个，可以多个迭代下载：：pk指向Accessory.id
    :param request:
    :return: attachment
    """
    pk = request.POST.get('pk')
    file_name = str(Accessory.objects.get(pk=pk).accessory)
    # 问题.txt <class 'django.db.models.fields.files.FieldFile'>
    file_dir = os.path.join(BASE_DIR, 'media', file_name)

    file = open(file_dir, 'rb')

    response = FileResponse(file)
    response['Content-Type'] = 'application/octet-stream'
    response['Content-Disposition'] = 'attachment;filename={}'. \
        format(file_name).encode('utf-8')

    return response


def excel_model_export(request, model, query_mark, filename,
                       sheet_name, sheet_head_data):
    """
    7:excel表格模版下载，用于B端导入表格格式下载
    :param request:
    :param model:
    :param query_mark:
    :param filename:
    :param sheet_name:
    :param sheet_head_data:
    :return: django.response
    """
    excel_abs_dir = os.path.join(BASE_DIR, 'media', 'excels', filename)

    # excel填充数据
    data = [[x for x in sheet_head_data.keys()]]  # 列表解析加载"sheet"标题行

    workbook = openpyxl.Workbook()  # 创建文件对象
    create_sheet = workbook.create_sheet(sheet_name, 0)  # 初始位置创建sheet对象
    # 设置表头背景色
    fp = PatternFill(fill_type='solid', fgColor="77DDFF")  # 浅蓝色

    for index, width in enumerate([x for x in sheet_head_data.values()]):
        create_sheet.cell(row=1, column=index + 1).fill = fp  # 第一行加浅蓝色
        cm = get_column_letter(index + 1)  # 根据序列生成对应列标号，第一列
        create_sheet.column_dimensions[cm].width = width  # 对应字段名设置列宽

    for row_index, row in enumerate(data):  # 数据写入到单元格中
        for col_index, col in enumerate(row):
            # 对"sheet"cell数据写入
            create_sheet.cell(row_index + 1, col_index + 1).value = col
            # 字体设置，颜色等，样式
            # sheet.cell(row_index+1, col_index+1).font = Font(
            #     color=colors.BLUE
            # )
            # 设置文字位置，对其方式=左对齐
            create_sheet.cell(row_index + 1,
                              col_index + 1).alignment = Alignment(
                horizontal='left',  # 水平：center left，right
                vertical='center'  # 垂直: top,bottom,center
            )

    del workbook['Sheet']  # 删除workbook中初始'Sheet'表名
    workbook.save(excel_abs_dir)  # 保存文件到本地, save调用了close

    # 返回文件流于浏览器
    file = open(excel_abs_dir, 'rb')
    response = FileResponse(file)
    response['Content-Type'] = 'application/octet-stream'
    response['Content-Disposition'] = 'attachment;filename={}'. \
        format(filename).encode('utf-8')  # filename可以另外写一个函数动态生成
    return response


def excel_export(request, excel_data, filename,
                 sheet_name, sheet_head_data):
    """前端传递值：None;
    8:(下载Excel表格);
    :param excel_data:-->表格数据
    :param filename: 客户端下载excel文件命名。（本接口使用query_mark固定值）;
    :param sheet_name: excel "sheet"命名;
    :param sheet_head_data:-->dict: "sheet"第一行数据，需要字段名，列宽，颜色等信息（可选）
    :return: return FileResponse(filename) or "msg" if rc."key" is None;
    """
    excel_directory = os.path.join(BASE_DIR, 'media', 'excels')
    excel_abs_dir = os.path.join(excel_directory, filename)

    max_row_num = 100000  # 最大导出行数
    if not os.path.exists(excel_directory):
        os.makedirs(excel_directory)

    # excel填充数据
    data = [[x for x in sheet_head_data.keys()]]  # 列表解析加载"sheet"标题行
    # queryset数据转化data = [['1', '邓平', '25'], ['2', '云云', '23']]
    for sheet_num, data_dic in enumerate(excel_data):
        data_lst = [sheet_num + 1]  # 序号num
        for value in data_dic.values():
            data_lst.append(value)
        data.append(data_lst)
    # 限制最大导出行数
    if len(data) > max_row_num:
        data = data[: max_row_num]

    workbook = openpyxl.Workbook()  # 创建文件对象
    create_sheet = workbook.create_sheet(sheet_name, 0)  # 初始位置创建sheet对象
    # 设置表头背景色
    fp = PatternFill(fill_type='solid', fgColor="77DDFF")  # 浅蓝色

    for index, width in enumerate([x for x in sheet_head_data.values()]):
        create_sheet.cell(row=1, column=index + 1).fill = fp  # 第一行加浅蓝色
        cm = get_column_letter(index + 1)  # 根据序列生成对应列标号，第一列
        create_sheet.column_dimensions[cm].width = width  # 对应字段名设置列宽

    for row_index, row in enumerate(data):  # 数据写入到单元格中
        for col_index, col in enumerate(row):
            # 对"sheet"cell数据写入
            create_sheet.cell(row_index + 1, col_index + 1).value = col
            # 字体设置，颜色等，样式
            # sheet.cell(row_index+1, col_index+1).font = Font(
            #     color=colors.BLUE
            # )
            # 设置文字位置，对其方式=左对齐
            create_sheet.cell(row_index + 1,
                              col_index + 1).alignment = Alignment(
                horizontal='left',  # 水平：center left，right
                vertical='center'  # 垂直: top,bottom,center
            )

    del workbook['Sheet']  # 删除workbook中初始'Sheet'表名
    workbook.save(excel_abs_dir)  # 保存文件到本地, save调用了close

    # 返回文件流于浏览器
    file = open(excel_abs_dir, 'rb')
    response = FileResponse(file)
    response['Content-Type'] = 'application/octet-stream'
    response['Content-Disposition'] = 'attachment;filename={}'. \
        format(filename).encode('utf-8')  # filename可以另外写一个函数动态生成
    return response


def excel_import(request, model, mode_fields, excel_file, min_row=3):
    """
    9:导入excel数据到数据库
    :param request:
    :param model: 数据库模型名称
    :type model: class
    :param mode_fields: 数据库字段名称
    :param excel_file: excel表格名称
    :param min_row : 有效数据，min_min=3，从excel第三行开始读取数据
    :return:  str
    <generator object Worksheet._cells_by_row at 0x7fb098b4e890> <Worksheet "Sheet2">
    """
    data = []
    try:
        workbook = openpyxl.load_workbook(excel_file)
        worksheet = workbook.worksheets[2]  # 获取sheet表格，type: list
        max_row_num = 5000  # 最大导入行数
        for index, row in enumerate(worksheet.iter_rows(min_row=min_row)):
            # min_row: excel表第几行数据开始读取
            if (index + 1) > max_row_num:
                break
            else:
                data_cell = []
                for cell in row:
                    # 对cell.value做数据处
                    if type(cell.value) is str:
                        data_cell.append(cell.value.strip())  # 去除str左右空格
                    else:
                        data_cell.append(cell.value)
                # print(data_cell)
                data_dict = dict(zip(mode_fields, data_cell))  # 合并为字典
                # print(index, data_dict)
                try:
                    model(**data_dict).save()  # 数据库写入
                except Exception as e_write:
                    data.append('第{}行数据错误：{}'.format(index + min_row, e_write))
        workbook.close()
        msg = '执行完成！'
    except Exception as e:
        msg = e
    finally:
        print(msg)
        for i in data:
            print(i)
        return msg, data
